#!/usr/bin/python3
# -*- coding: utf-8 -*-

"""
需要先安装 mysql-connector-python, openpyxl
"""

import mysql.connector
from openpyxl.styles import Border, Font, NamedStyle, Alignment
from openpyxl import Workbook

# 定义表格样式, 头部信息
header = ['字段名', '类型', '默认值', '允许为空', '是否主键', '拓展', '描述']
headerStyle = NamedStyle(name='headerStyle')
headerStyle.font = Font(bold=True)
headerStyle.alignment = Alignment(horizontal='center', vertical='center')
headerStyle.border = Border(top=Border().top)
# 定义指针
tb_cursor = [1, 1]

wb = Workbook()
ws = wb.active
ws.title = '数据库字段说明'


def writer(fields):
    # TODO: 处理非字符串类型变量
    _fields = [list(map(str, f)) for f in fields]
    print(_fields)
    for field in _fields:
        ws.append(field)
        tb_cursor[0] += 1


host = input('host: ')
user = input('user: ')
pwd = input('password: ')
database = input('database: ')


# 建立连接 创建指针
connect = mysql.connector.connect(user=user, database=database, password=pwd, host=host)
cursor = connect.cursor()
# 获取所有要遍历的表名
cursor.execute('show tables')
tables = [n[0] for n in cursor.fetchall()]

for table_name in tables:
    cursor.execute('''select `COLUMN_NAME`, `COLUMN_TYPE`, `COLUMN_DEFAULT`, `IS_NULLABLE`, `COLUMN_KEY`, `EXTRA`, `COLUMN_COMMENT` from information_schema.columns where table_schema = "%s" and table_name = "%s"''' % (database, table_name))
    # 指针往下一行
    tb_cursor[0] += 1
    # 写入表格名称, 应用头部样式, 合并单元格
    ws.cell(row=tb_cursor[0], column=tb_cursor[1], value=table_name).style = headerStyle
    ws.merge_cells(start_row=tb_cursor[0], start_column=tb_cursor[1], end_row=tb_cursor[0], end_column=tb_cursor[1] + 6)
    # 换行, 写入表头
    tb_cursor[1] = 1
    tb_cursor[0] += 1
    ws.append(header)
    table_fields = cursor.fetchall()
    print('正在处理: ' + table_name + '...')
    writer(table_fields)
    tb_cursor[0] += 1

cursor.close()
connect.close()

wb.save("table_fields.xlsx")

'''
COLUMN_NAME, 
COLUMN_TYPE, 
COLUMN_DEFAULT, 
IS_NULLABLE, 
COLUMN_KEY, 
EXTRA, 
COLUMN_COMMENT
'''
